const mysql = require('mysql2/promise');

const dbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs'
};

async function createSystemMessagesTableNew() {
    let connection;

    try {
        console.log('🔌 连接到数据库...');
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功');

        // 创建新的系统消息表
        const createTableSQL = `
            CREATE TABLE IF NOT EXISTS \`system_messages\` (
              \`id\` bigint NOT NULL AUTO_INCREMENT COMMENT '消息ID',
              \`senderId\` int NOT NULL DEFAULT '0' COMMENT '发送者ID，0表示系统',
              \`senderName\` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '系统' COMMENT '发送者名称',
              \`messageBody\` json NOT NULL COMMENT '消息体，存储完整的消息数据格式',
              \`messageType\` tinyint NOT NULL COMMENT '消息类型：1-个人消息，2-广播消息',
              \`receiverId\` int DEFAULT NULL COMMENT '接收者ID，广播消息可为空',
              \`isRead\` tinyint NOT NULL DEFAULT '0' COMMENT '是否已读：0-未读，1-已读',
              \`createdAt\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
              \`updatedAt\` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
              PRIMARY KEY (\`id\`),
              KEY \`idx_sender_created\` (\`senderId\`,\`createdAt\`),
              KEY \`idx_receiver_created\` (\`receiverId\`,\`createdAt\`),
              KEY \`idx_message_type_created\` (\`messageType\`,\`createdAt\`),
              KEY \`idx_is_read_created\` (\`isRead\`,\`createdAt\`),
              KEY \`idx_created_at\` (\`createdAt\`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统消息表'
        `;

        // 先删除现有表（如果存在）
        console.log('🗑️ 删除现有的 system_messages 表（如果存在）...');
        await connection.execute('DROP TABLE IF EXISTS system_messages');
        console.log('✅ 现有表已删除');

        console.log('📋 创建新的 system_messages 表...');
        await connection.execute(createTableSQL);
        console.log('✅ 新的 system_messages 表创建成功');

        // 插入示例数据
        console.log('📝 插入示例数据...');

        // 示例广播消息（大小开奖）
        const sampleDiceMessage = {
            act_id: 260,
            code: 0,
            data: {
                c: '本次游乐场乾坤八卦开1,2,3大双,共奖出1000金500银两!',
                h: '15:30',
                id: Date.now(),
                n: '系统',
                rid: 1000,
                t: 6
            }
        };

        // 示例广播消息（赤壁开奖）
        const sampleChibiMessage = {
            act_id: 260,
            code: 0,
            data: {
                c: '三国鸭胜负已分,获胜国给支持者发放战利品2000金1000银',
                h: '15:35',
                id: Date.now() + 1,
                n: '系统',
                rid: 1000,
                t: 6
            }
        };

        // 示例个人消息（中奖通知）
        const samplePersonalMessage = {
            act_id: 10,
            info_id: 1000,
            state: 1,
            receiverCharacterId: 123,
            messageContent: '恭喜,您在大小玩法中中奖，获得100金币！'
        };

        // 插入示例数据
        await connection.execute(
            `INSERT INTO system_messages (
                senderId, senderName, messageBody, messageType, 
                receiverId, isRead, createdAt, updatedAt
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
            [
                1000, '系统', JSON.stringify(sampleDiceMessage), 2,
                null, 1, new Date(), new Date()
            ]
        );

        await connection.execute(
            `INSERT INTO system_messages (
                senderId, senderName, messageBody, messageType, 
                receiverId, isRead, createdAt, updatedAt
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
            [
                1000, '系统', JSON.stringify(sampleChibiMessage), 2,
                null, 1, new Date(), new Date()
            ]
        );

        await connection.execute(
            `INSERT INTO system_messages (
                senderId, senderName, messageBody, messageType, 
                receiverId, isRead, createdAt, updatedAt
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
            [
                1000, '系统', JSON.stringify(samplePersonalMessage), 1,
                123, 0, new Date(), new Date()
            ]
        );

        console.log('✅ 示例数据插入成功');

        // 验证表结构
        console.log('🔍 验证表结构...');
        const [columns] = await connection.execute('DESCRIBE system_messages');
        console.log('📋 表结构:');
        columns.forEach(col => {
            console.log(`  - ${col.Field}: ${col.Type} ${col.Null === 'NO' ? '(NOT NULL)' : '(NULL)'} ${col.Key ? `[${col.Key}]` : ''} ${col.Comment ? `// ${col.Comment}` : ''}`);
        });

        // 验证数据
        const [count] = await connection.execute('SELECT COUNT(*) as count FROM system_messages');
        console.log(`✅ 表中现有 ${count[0].count} 条记录`);

        console.log('🎉 新的 system_messages 表创建完成！');
        console.log('');
        console.log('📋 使用说明:');
        console.log('1. 广播消息 (messageType = 2):');
        console.log('   - receiverId 为 NULL');
        console.log('   - isRead 默认为 1 (已读)');
        console.log('   - messageBody 存储完整的广播消息格式');
        console.log('');
        console.log('2. 个人消息 (messageType = 1):');
        console.log('   - receiverId 指定接收者ID');
        console.log('   - isRead 默认为 0 (未读)');
        console.log('   - messageBody 存储完整的个人消息格式');
        console.log('');
        console.log('3. 消息体格式:');
        console.log('   - 广播消息: { act_id, code, data: { c, h, id, n, rid, t } }');
        console.log('   - 个人消息: { act_id, info_id, state, receiverCharacterId, messageContent }');

    } catch (error) {
        console.error('❌ 创建表失败:', error);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

// 运行创建脚本
if (require.main === module) {
    createSystemMessagesTableNew()
        .then(() => {
            console.log('✅ 创建脚本执行完成');
            process.exit(0);
        })
        .catch((error) => {
            console.error('❌ 创建脚本执行失败:', error);
            process.exit(1);
        });
}

module.exports = { createSystemMessagesTableNew };
